#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
   @Author: 即将拥有人鱼线的yjw
   @Time: 2024/12/4 17:14
   @File: commonDict.py
   @desc:
   客户类型
   证件类型
   职业/行业
   国籍
   交易类型
   销售商类型
   成功状态
   风险等级
   产品类型B0043
   学历
"""
import random
import re

from CommonScripts.blade import python_exec_sql



def get_client_type(clientType):
    '''
    客户类型转换
    :param clientType: 客户类型：个人、机构、产品
    :return:
    '''
    dict_entries = {
        '个人': '0',
        '机构': '1',
        '产品': '2'
    }
    if clientType not in dict_entries:
        raise ValueError(f"未知的客户类型: {clientType}")
    return dict_entries[clientType]

def get_id_type(clientType, idType=None, env=None):
    '''
    获取客户证件类型
    :param env:
    :param clientType: 客户类型：个人、机构、产品
    :param idType: 中文类型
    :return:
    '''
    dict_entries = {
        '个人': 'B2202',
        '机构': 'B2203',
        '产品': 'B2204'
    }
    if clientType not in dict_entries:
        raise ValueError(f"未知的客户类型: {clientType}")
    return get_hspbs_dict(clientType,dict_entries[clientType],idType,env)


def get_occupation_code(clientType, occupationName=None, env=None):
    '''
    获取个人职业或者机构行业
    :param clientType: 客户类型：个人、机构、产品
    :param occupationName: 职业/行业
    :param env: 环境信息
    :return: 职业/行业编码
    '''
    dict_entries = {
        '个人': 'B0006',
        '机构': 'B0011',
        '产品': 'B0011'
    }
    if clientType not in dict_entries:
        raise ValueError(f"未知的客户类型: {clientType}")
    sub_entry = get_hspbs_dict(clientType,dict_entries[clientType],occupationName,env)
    return format_sub_entry(sub_entry)


def format_sub_entry(sub_entry):
    """
    格式化 sub_entry，使其以大写字母开头，并将后四位替换为 '0000'
    :param sub_entry: 原始的 sub_entry 字符串
    :return: 格式化后的 sub_entry 字符串
    """
    if not sub_entry or len(sub_entry) < 5:
        return sub_entry  # 职业只有两位，直接返回原字符串,

    if re.match(r'^[A-Z]', sub_entry):  # 检查是否以大写字母开头
        return sub_entry[0] + '0000'  # 职业5位，以大写字母开头，替换后四位为 '0000'，只取大的行业，比如制造业
    return sub_entry  # 如果不匹配条件，返回原始字符串

def get_nationality_code(clientType, nationalityName=None, env=None):
    '''
    获取国籍代码
    :param clientType: 客户类型
    :param nationalityName: 国籍名称
    :param env: 环境信息
    :return:
    '''
    return get_hspbs_dict(clientType,'B0004',nationalityName,env)

def get_gender_code(clientType, nationalityName=None, env=None):
    '''
    获取国籍代码
    :param clientType: 客户类型
    :param nationalityName: 国籍名称
    :param env: 环境信息
    :return:
    '''
    return get_hspbs_dict(clientType,'B0005',nationalityName,env)

def get_trade_status_code(clientType=None, tradeStatus=None, env=None):
    '''
    获取交易状态
    :param clientType: 客户类型
    :param tradeStatus: 国籍名称
    :param env: 环境信息
    :return:
    '''
    dict_entries = {
        '成功': '1',
        '失败': '2'
    }
    if tradeStatus:
        return dict_entries[tradeStatus]
    else:
        return '1'

def get_agency_no_code(clientType=None, agency_type = None,agency_no =None, env=None):
    dict_entries = {
        '直销': '0',
        '代销': '1'
    }
    agency_type_code = ''
    if agency_type:
        agency_type_code = dict_entries[agency_type]
    sql = f"select agency_type, agency_no  from hsfund.fnd_agency"
    try:
        result_info = python_exec_sql(sql, env=env)
        result = convert_to_lowercase(result_info)
        if not result:
            raise ValueError(f"未找到任何销售商，销售商类型: {agency_type}，销售商编码{agency_no}")
        if agency_no:
            # 查找特定的销售商类型
            matching_items = [item for item in result if agency_no in item['agency_no']]
            if matching_items:
                return agency_no
            else:
                raise ValueError(f"未找到匹配的销售商: {agency_no}")
        else:
            if agency_type_code:
                matching_items = [item for item in result if agency_type_code in item['agency_type']]
                item = random.choice(matching_items)
                return item['agency_no']
            else:
                item = random.choice(result)
                return item['agency_no']
    except Exception as e:
        print(f"发生错误: {e}")


def get_education_code(clientType=None, educationName=None, env=None):
    '''
    获取学历
    :param clientType:
    :param occupationName:
    :param env:
    :return:
    '''
    return get_hspbs_dict(clientType, 'B0002', educationName, env)

def get_product_code(clientType=None, fundTypeName=None, productName=None, env=None):
    '''
    获取产品编号，如果指定产品类型不存在，则插入一条产品信息
    :param clientType:
    :param fundTypeName:指定产品类型
    :param productName:指定产品名称
    :param env:环境信息
    :return:产品编号
    '''

    sql = f"select product_code, product_name,product_status  from hsfund.fnd_fund_info"
    fundType = ''
    if fundTypeName:
        fundType = get_product_type_code(clientType,fundTypeName,env)
        sql = f"select product_code, product_name,product_status  from hsfund.fnd_fund_info where fund_type = '{fundType}'"
    try:
        result_info = python_exec_sql(sql, env=env)
        result = convert_to_lowercase(result_info)
        if not result:
            if fundTypeName:
                product_code = str(random.randint(100000,999999))
                product_name = '产品'+product_code
                sql = f"INSERT INTO hsfund.fnd_fund_info (product_code,product_name,product_status,manager_name,currency_code,fund_type,ta_no,setup_date,confirm_days,data_date,manager_no,fund_par_value,issue_price,issue_date,trustee_no,trustee_name,prod_max_balance,prod_min_balance,share_low_maturity,fund_subscirbe_maturity,fund_close_maturity,min_oact_count,client_min_maturity,excess_cfm_rate,fund_feature,subscribe_schema,duration_limit,share_classify,fund_issue_type,prd_issue_real_bala,fund_setup_confirm_ratio,ipo_fail_date,feer_calculation_mode,feer_consolidate_mode,fee_calculation_mode,exceed_part,bonus_mode,sub_interest_dealtype,redeem_fare_fund_assert,convert_fare_fund_assert,manager_accrual,total_unit_dividends,maturity_date,is_conversion_preferential,fund_product_type,nav_valu_bit,contract_end_date,active_dt,ipo_end_date,is_strongration_flag,is_yeb_flag,is_short_term_finance_flag,product_name_en,risk_level,fund_name,fund_stat_code,income_carryover_freq,is_carried_forward,monthly_cashing_date,official_code,redemption_clos_type,length_of_closure,unit_of_closure,main_fund_code,side_pocket_flag,remarks,exchange_logo,product_source,main_product_code,product_manager,product_template_no,open_comb,is_cross_border,product_principal_structure,product_series,investment_nature,operation_method,product_income_type) VALUES('{product_code}','{product_name}','0','0','CNY','{fundType}','HS',0,0,NULL,' ',NULL,NULL,0,NULL,NULL,0.00,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,0.00000000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',' ',NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,'')"
                python_exec_sql(sql, env=env)
                return product_code
            else:
                raise ValueError(f"未找到任何产品信息{productName}")
        if productName:
            # 查找特定的销售商类型
            matching_items = [item for item in result if productName in item['product_name']]
            if matching_items:
                return matching_items[0]['product_code']
            else:
                raise ValueError(f"未找到匹配的产品: {productName}")
        else:
            if fundTypeName:
                matching_items = [item for item in result if fundType in item['fund_type']]
                item = random.choice(matching_items)
                return item['product_code']
            else:
                item = random.choice(result)
                return item['product_code']
    except Exception as e:
        print(f"发生错误: {e}")

def get_product_type_code(clientType=None,fundTypeName=None,env=None):
    '''
    获取产品类型编码
    :param clientType:
    :param fundType:
    :param env:
    :return:
    '''
    return get_hspbs_dict(clientType, 'B0043', fundTypeName, env)

def get_business_flag(clientType, businessName=None, env=None):
    '''
    获取交易类型编码
    :param clientType:  客户编码
    :param businessName: 交易类型名称
    :param env: 环境信息
    :return: 交易类型编码
    '''
    sql = f"select business_name, business_flag  from hsfund.fnd_business_flag"
    try:
        result_info = python_exec_sql(sql, env=env)
        result = convert_to_lowercase(result_info)
        if not result:
            raise ValueError(f"未找到任何交易类型，客户类型: {clientType}")
        if businessName:
            # 查找特定的证件类型
            matching_items = [item for item in result if businessName in item['business_name']]
            if matching_items:
                return matching_items[0]['business_flag']
            else:
                raise ValueError(f"未找到匹配的交易类型: {businessName}")
        else:
            item = random.choice(result)
            return item['business_flag']
    except Exception as e:
        print(f"发生错误: {e}")


def convert_to_lowercase(data):
    """
    将字典列表中的所有键和值转换为小写
    :param data: 包含字典的列表
    :return: 转换后的包含字典的列表
    """
    return [{k.lower(): str(v) for k, v in item.items()} for item in data]

def get_hspbs_dict(clientType, dict_entry, dict_prompt=None, env=None):
    '''
    获取字典代码
    :param clientType: 客户类型：个人、机构、产品
    :param dict_entry: 字典类型编码：比如职业代码：B0006
    :param dict_prompt: 字典项名称
    :param env:环境信息
    :return: 字典编码 比如其他专业技术人员：14
    '''
    if dict_prompt:
        sql = f"select dict_prompt, sub_entry from hspbs.pbs_dictionary where dict_entry = '{dict_entry}' and dict_prompt = '{dict_prompt}'"
    else:
        sql = f"select dict_prompt, sub_entry from hspbs.pbs_dictionary where dict_entry = '{dict_entry}'"
    print(sql)
    try:
        result_info = python_exec_sql(sql, env=env)
        result = convert_to_lowercase(result_info)
        if not result:
            if dict_prompt:
                return dict_prompt
            else:
                raise ValueError(f"未找到任何字典，客户类型: {clientType}，字典类型编码{dict_entry},字典: {dict_prompt}")
        else:
            item = random.choice(result)
            return item['sub_entry']
    except Exception as e:
        print(f"发生错误: {e}")



def get_user_values(keywords=None, table_field=None, env='mysql'):
    '''
    通过用户描述生成的键值对，替换成表对应字段的值，如果不存在，返回原值

    :param keywords: 用户描述生成的键值对字典，例如：
                     {'申请日期': '20241206', '发生日期': '20241208'}
    :param table_field: 表字段信息列表，每个元素是一个包含字段名和注释的字典，例如：
                        [{'column_name': 'apply_date', 'column_comment': '申请日期'},
                         {'column_name': 'occur_date', 'column_comment': '发生日期'}]
    :return: 替换后的键值对字典，例如：{'apply_date': '20241206', 'occur_date': '20241208'}
    '''
    # 确保 keywords 是一个字典，table_field 是一个列表
    if not isinstance(keywords, dict):
        keywords = {}
    if not isinstance(table_field, list):
        table_field = []

    # 创建从注释到字段名的映射
    comment_to_column = {item.get('column_comment'): item.get('column_name') for item in table_field}

    # 创建从字段名到字段名的映射（用于直接匹配）
    column_names = {item.get('column_name'): item.get('column_name') for item in table_field}

    result = {}
    for key, value in keywords.items():
        # 尝试使用注释映射查找字段名
        column_name = comment_to_column.get(key)
        if column_name is None:
            # 如果未找到，则尝试直接使用字段名匹配
            column_name = column_names.get(key)

        if column_name is not None:
            result[column_name] = value
        else:
            # 如果没有找到匹配项，保留原始键名
            result[key] = value
    client_type_code = result.pop('client_type',None)
    print(f"result: {result}")
    if not client_type_code:
        client_type_code = '0'
    dict_entries = {
        '0': '个人',
        '1': '机构',
        '2': '产品'
    }
    client_type = dict_entries[client_type_code]
    result_copy = result.copy()
    for key,value in result.items():
        if key == 'id_type':
            value = get_id_type(client_type,value,env)
            result_copy.update({key: value})
        elif key == 'occupation_code':
            value = get_occupation_code(client_type,value,env)
            result_copy.update({key: value})
        elif key == 'nationality_code':
            value = get_nationality_code(client_type,value,env)
            result_copy.update({key: value})
        elif key == 'trade_status':
            value = get_nationality_code(client_type, value, env)
            result_copy.update({key: value})
        elif key == 'agency_no':
            value = get_agency_no_code(client_type,agency_no=value,env=env)
            if not value:
                value = get_agency_no_code(client_type,agency_type=value,env=env)
            result_copy.update({key: value})
        elif key == 'product_code':
            value = get_product_code(client_type,productName=value,env=env)
            if not value:
                value = get_product_code(client_type,fundTypeName=value,env=env)
            result_copy.update({key: value})
        elif key == 'business_flag':
            value = get_business_flag(client_type,value,env)
            result_copy.update({key: value})
        elif key == 'client_name':
            result_copy.update({'client_full_name': value})
        elif key == 'gender_code':
            value = get_gender_code(client_type,value,env)
            result_copy.update({'gender_code': value})
    print(f"result_copy:{result_copy}")
    return result_copy




def get_table_data(tableName,env='mysql'):
    '''
    获取表的字段名称、字段类型、描述
    :param tableName: 表名
    :param env: 环境
    :return:
    '''
    sql = f"SELECT COLUMN_NAME AS 'field',COLUMN_TYPE AS 'type',IS_NULLABLE AS 'nullable',COLUMN_DEFAULT AS 'default', COLUMN_COMMENT AS 'comment'FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'hsfund'  AND TABLE_NAME = '{tableName}'"
    result = python_exec_sql(sql, env=env)
    print(convert_to_lowercase(result))

if __name__ == '__main__':
    # id_type = get_id_type('个人',  env='mysql')
    # print(id_type)
    # occupation_code = get_occupation_code('机构', env=None)
    # print(occupation_code)
    # nationality_code = get_nationality_code('个人','中国')
    # print(nationality_code)
    # business_flag = get_business_flag('机构',businessName='认购', env=None)
    # print(business_flag)
    # trade_status = get_trade_status_code(tradeStatus='失败')
    # # print(trade_status)
    # agencyNo = get_agency_no_code(agency_type='代销',agency_no='015')
    # print(agencyNo)
    # product_code = get_product_code(fundTypeName='养老目标基金')
    # print(product_code)
    result = get_table_data('fnd_request',env='oracle_trust')
    print(result)